<?php
/**
 * PlaceController is for all place list and view.
 * 
 * Bocheng Chen, 18.07.2012
 * 
 * LBB Solutions Oy 版权所有 @ 2012
 */
class PlaceController extends Controller
{
	public function actionIndex()
	{
		if (!Yii::app()->request->isAjaxRequest) {
			$this->render('index');
			Yii::app()->end();
		}
		
		//get markers
		$bounds = Yii::app()->request->getPost('bounds');
		$params = array();
		$s_bounds = null;
		if (count($bounds) == 4) {
			//TODO: check the bounds: (SW, NE)
			$s_bounds = ' AND m.latitude>:sw_lat AND m.latitude<:ne_lat AND m.longitude>:sw_lng AND m.longitude<:ne_lng ';
			$params = array(
				':sw_lat' => $bounds[0],
				':ne_lat' => $bounds[2],
				':sw_lng' => $bounds[1],
				':ne_lng' => $bounds[3],
			);
		}
		
		//q=?
		$tag_key = Yii::app()->request->getPost('q');
		$sql_where = '';
		if (!empty($tag_key)) {
			$s_bounds = null;//remove all bounds
			$params = array();//remove all params
			
			$params[':tag'] = "%{$tag_key}%";
			$sql_where .= ' AND (m.name LIKE :tag
				OR m.category_key LIKE :tag 
				OR t.cuisine_style LIKE :tag 
				OR t.cuisine_type LIKE :tag 
				OR t.meal_served LIKE :tag 
				OR t.payment_options LIKE :tag 
				OR t.service_languages LIKE :tag 
				OR t.alcohol_license LIKE :tag 
				OR t.dining_ambience LIKE :tag 
				OR t.car_parking LIKE :tag 
				OR t.additional_details LIKE :tag) ';
		}
		
		//get position (optional)
		$s_distance = ', NULL AS distance ';
		$pos = Yii::app()->request->getPost('position');
		if (!empty($pos)) {
			if (!empty($pos['lat']) and !empty($pos['lng'])) {
				$s_distance = ', (6371 * acos(cos(radians(:lat)) * cos(radians(m.latitude)) * cos(radians(m.longitude) - radians(:lng)) + sin(radians(:lat)) * sin(radians(m.latitude)))) AS distance ';
				$params[':lat'] = (double)$pos['lat'];
				$params[':lng'] = (double)$pos['lng'];
			}
		}
		
		//only for published
		$s_status = ' AND m.status=1 ';
		
		//1. fetch restaurants
		$sql_restaurants = 'SELECT m.id, m.seo_id, m.name, m.phone, m.address, m.biz_area, m.postcode, m.city, m.country_code, m.currency_code,
			m.url_logo, m.url_site, m.latitude, m.longitude, m.opening, m.brief, m.status,
			m.category_key, m.enable_rewards, t.cuisine_style, t.cuisine_type,
			COUNT(s.id) AS offers, MAX(s.id) AS id_special, MAX(s.published_time) AS updating_special,
			(SELECT COUNT(p.id) FROM tbl_merchant_program p WHERE p.id_merchant=m.id AND p.trash=0 AND p.status=1) AS rewards ' . $s_distance . '
			FROM tbl_merchant_account m
			LEFT JOIN tbl_merchant_tags t ON m.id=t.id_merchant
			LEFT JOIN tbl_merchant_special s ON s.id_merchant=m.id AND s.trash=0 AND s.status=1 AND s.start_date<NOW() AND s.end_date>NOW()
			WHERE m.status=1 AND m.name IS NOT NULL AND m.latitude IS NOT NULL AND m.longitude IS NOT NULL
			  ' . $s_status . $s_bounds . $sql_where . '
			GROUP BY m.id
			ORDER BY rewards DESC, offers DESC, updating_special DESC, m.name ASC
			LIMIT 30';
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_restaurants);
		$rows = $command->queryAll(true, $params);
		$connection->active = false;
		
		$this->renderPartial('_venues', array(
			'rows' => $rows,
		));
	}
	
	public function actionView($id)
	{
		if (empty($id)) {
			throw new CHttpException(404, 'The parameter "id" is missing!');
		}
		
		//0. fetch merchants + tags
		$sql_merchant = 'SELECT m.id, m.seo_id, m.name, m.phone, m.address, m.biz_area, m.postcode, m.city, m.country_code, m.currency_code,
			m.url_logo, m.url_site, m.url_facebook_page, m.latitude, m.longitude, m.opening, m.brief, m.url_offer_img, 
			m.category_key, m.enable_rewards,
			t.cuisine_type, t.cuisine_style, t.meal_served, t.payment_options, t.service_languages, 
			t.alcohol_license, t.dining_ambience, t.car_parking, t.additional_details,
			(SELECT COUNT(g.id) FROM tbl_merchant_gallery g WHERE g.id_merchant=m.id) AS gallery,
			(SELECT COUNT(s.id) FROM tbl_merchant_special s WHERE s.id_merchant=m.id AND s.trash=0 AND s.status=1 AND s.start_date<NOW() AND s.end_date>NOW()) AS offers,
			(SELECT COUNT(p.id) FROM tbl_merchant_program p WHERE p.id_merchant=m.id AND p.trash=0 AND p.status=1) AS rewards
			FROM tbl_merchant_account m
			LEFT JOIN tbl_merchant_tags t ON m.id=t.id_merchant
			WHERE (m.id=:id OR m.seo_id=:id)';
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_merchant);
		$row = $command->queryRow(true, array('id'=>$id));
		if (empty($row)) {
			$connection->active = false;
			throw new CHttpException(404, 'The parameter "id" is invalid!');
		}
		
		//2. get merchant's open hours
		$sql_hours = 'SELECT week_no, open_hour, close_hour FROM tbl_merchant_open_hours
			WHERE id_merchant=:id_merchant ORDER BY week_no ASC';
		$command = $connection->createCommand($sql_hours);
		$aHours = $command->queryAll(true, array('id_merchant'=>$row['id']));
		
		//3. get user's follow info if loged in
		$status = array();
		if (!Yii::app()->user->isGuest) {
			//checking the following status
			$sql_follow = 'SELECT id_user FROM tbl_user_follow_merchant WHERE id_user=:id_user AND id_merchant=:id_merchant';
			$command = $connection->createCommand($sql_follow);
			$follow_row = $command->queryRow(true, array(':id_merchant'=>$row['id'], ':id_user'=>Yii::app()->user->id));
			if (empty($follow_row['id_user'])) $status['follow'] = false;
			else $status['follow'] = true;
		}
		$connection->active = false;
		
		//set seo title
		$this->header['title'] = $row['name'];
		$this->pageTitle = $this->header['title'];
		$url_picture = BUtils::GetBusinessPictureUrl($row['url_logo']);
		$this->header['image'] = Yii::app()->params['siteRoot'] . $url_picture;
		$this->header['description'] = strip_tags(trim($row['brief']));
		
		$model = new LoginForm('login');
		$this->render('view', array(
			'row' => $row,
			'aHours' => $aHours,
			'status' => $status,
			'model'=>$model,
		));
	}
	
	public function actionOffers($id) {
		if (!Yii::app()->request->isAjaxRequest) {
			throw new CHttpException(404, 'The requested page is only for AJAX request.');
		}
		if (empty($id)) {
			throw new CHttpException(404, 'The parameter "id" is missing!');
		}
		
		$sql_offers = 'SELECT s.id, m.seo_id, s.id_merchant, s.url_picture, s.content, s.show_hours, s.start_hour, s.end_hour, s.week_days,
			s.price, s.value, s.discount, s.highlight, s.type, s.max_number,
			UNIX_TIMESTAMP(s.start_date) AS uts_start_date, UNIX_TIMESTAMP(s.end_date) AS uts_end_date,
			m.name, m.category_key, m.url_logo, m.url_offer_img, m.phone, m.address, m.biz_area, m.postcode, m.city, m.latitude, m.longitude
			FROM tbl_merchant_special s
			INNER JOIN tbl_merchant_account m ON m.id=s.id_merchant
			WHERE s.id_merchant=:id_merchant
			AND s.trash=0 AND s.status=1 AND s.end_date>NOW() ';
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_offers);
		$rows = $command->queryAll(true, array(':id_merchant' => $id));
		$connection->active = false;
		
		$this->renderPartial('_offers', array('rows'=>$rows));
	}
	
	public function actionRewards($id) {
		if (!Yii::app()->request->isAjaxRequest) {
			throw new CHttpException(404, 'The requested page is only for AJAX request.');
		}
		if (empty($id)) {
			throw new CHttpException(404, 'The parameter "id" is missing!');
		}
		
		$sql_rewards = 'SELECT id, id_merchant, title, description, required_points
			FROM tbl_merchant_program 
			WHERE id_merchant=:id_merchant AND trash=0 AND status=1
			ORDER BY required_points';
		$sql_terms = 'SELECT caption, terms FROM tbl_merchant_program_terms WHERE id_merchant=:id_merchant ';
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_rewards);
		$rows = $command->queryAll(true, array(':id_merchant' => $id));
		$command = $connection->createCommand($sql_terms);
		$program = $command->queryRow(true, array(':id_merchant' => $id));
		$connection->active = false;
		
		$this->renderPartial('_rewards', array('rows'=>$rows, 'program'=>$program));
	}
	
	public function actionGallery($id) {
		if (!Yii::app()->request->isAjaxRequest) {
			throw new CHttpException(404, 'The requested page is only for AJAX request.');
		}
		if (empty($id)) {
			throw new CHttpException(404, 'The parameter "id" is missing!');
		}
		
		$sql_gallery = 'SELECT id, url_picture, title, type, id_user, UNIX_TIMESTAMP(create_time) AS ts_create_time
			FROM tbl_merchant_gallery
			WHERE id_merchant=:id_merchant
			ORDER BY create_time DESC';
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_gallery);
		$rows = $command->queryAll(true, array(':id_merchant' => $id));
		$connection->active = false;
		
		$this->renderPartial('_gallery', array('rows'=>$rows));
	}

	public function actionFollow($id)
	{
		if (!Yii::app()->request->isAjaxRequest) {
			throw new CHttpException(404, 'The requested page is only for AJAX request.');
		}
		if (empty($id)) {
			throw new CHttpException(404, 'The parameter "id" is missing!');
		}
		if (Yii::app()->user->isGuest) {
			throw new CHttpException(404, 'Need to signed in to follow this merchant!');
		}

		$cancel = Yii::app()->request->getQuery('cancel');
		if ($cancel == 1) {
			$sql_follow = 'DELETE FROM tbl_user_follow_merchant WHERE id_user=:id_user AND id_merchant=:id_merchant ';
		} else {
			$sql_follow = 'INSERT INTO tbl_user_follow_merchant (id_user, id_merchant) VALUES (:id_user, :id_merchant) ';
		}
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql_follow);
		$command->execute(array(':id_merchant'=>$id, ':id_user' => Yii::app()->user->id));
		$connection->active = false;

		if ($cancel == 1) { echo '<i class="icon-heart"></i> ' . BUtils::T('follow_btn'); }
		else { echo '<i class="icon-heart"></i> ' . BUtils::T('follow_cancel_btn'); }
	}
	
	/**
	 *  Get tag links from a set of keys.
	 */
	public function getTagLinks($keyItems, $glue, $limit = 0) {
		$tagLinks = null;
		if (!empty($keyItems)) {
			$aRow = explode(',', $keyItems);
			foreach ($aRow as $i => $key) {
				if ($i++ >= $limit and $limit > 0) break;
				$tagLinks .= '<span class="categoryName">'.BUtils::T($key).'</span>' . $glue;
			}
		}
		return empty($tagLinks) ? '' : substr($tagLinks, 0, 0 - strlen($glue));
	}
}